﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

using System.Configuration;
using System.Web;
/// <summary>
/// Summary description for dal
/// </summary>
//namespace DBManager
//{
public class DBUtil
{
    public static SqlConnection connSql;
    public static string strConn = "";

    public DBUtil()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    public static string GetHostAddress()
    {
        string host = "192.168.1.31";
        try
        {
            host = System.Configuration.ConfigurationManager.AppSettings["idHost"];
        }
        catch (Exception) { }
        return host;
    }
    #region connection string
    public static void GetConnection()
    {
        try
        {
            if (HttpContext.Current.Application["ConnectionString"] != null)
                strConn = HttpContext.Current.Application["ConnectionString"].ToString();
            else
            {
                strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
                HttpContext.Current.Application["ConnectionString"] = strConn;
            }
        }
        catch (Exception ex)
        {
            //strConn = ConfigurationSettings.AppSettings["ConnectionString"];
            strConn = System.Configuration.ConfigurationManager.AppSettings["ConnectionString"];
            //HttpContext.Current.Application["ConnectionString"] = strConn;
            //throw ex;
        }
        finally
        {

        }

    }
    #endregion
    #region Open/close
    //public static void cnopen()
    //{
    //    if (connSql == null)
    //    {
    //        //files.writeLog("Hàm cnopen: Mở kết nối");
    //        strConn = ConfigurationSettings.AppSettings["ConnectionString"];
    //        connSql = new SqlConnection(strConn);
    //        connSql.Open();
    //    }
    //    else
    //    {
    //        //files.writeLog("Hàm cnopen: Tại sao connSql khác null");
    //        if (connSql.State != ConnectionState.Open)
    //        {
    //            //files.writeLog("Hàm cnopen: Tại sao connSql khác null mà trạng thái lại khác Open");               
    //            connSql.Open();
    //        }
    //    }
    //}
    //public static void cnclose()
    //{
    //    if (connSql != null)
    //    {
    //        if(connSql.State != ConnectionState.Closed)
    //            connSql.Close();
    //        connSql = null;
    //        //files.writeLog("Hàm cnclose: Đóng kết nối");
    //    }
    //    else
    //    {
    //        //files.writeLog("Hàm cnclose: Tại sao đã đóng kết nối");
    //    }
    //}
    #endregion
    #region execute querry command
    public static int Execute(string strSQL)
    {
        int val = 0;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            val = cmdSql.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            connSql.Close();
        }
        return val;
    }
    public static int Execute(string strSQL, SqlParameter param)
    {
        int val = 0;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            cmdSql.Parameters.Add(new SqlParameter(param.ParameterName, param.Value));
            val = cmdSql.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static int Execute(string strSQL, SqlParameter[] param)
    {
        int val = 0;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            for (int i = 0; i < param.Length; i++)
            {
                cmdSql.Parameters.Add(new SqlParameter(param[i].ParameterName, param[i].Value));
            }
            val = cmdSql.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    #endregion

    #region execute Scalar
    public static object ExecuteScalar(string strSQL)
    {
        object val = null;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            val = cmdSql.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static object ExecuteScalar(string strSQL, SqlParameter param)
    {
        object val = null;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            cmdSql.Parameters.Add(new SqlParameter(param.ParameterName, param.Value));
            val = cmdSql.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static object ExecuteScalar(string strSQL, SqlParameter[] param)
    {
        object val = null;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            for (int i = 0; i < param.Length; i++)
                cmdSql.Parameters.Add(new SqlParameter(param[i].ParameterName, param[i].Value));
            val = cmdSql.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            connSql.Close();
        }
        return val;
    }
    #endregion

    #region Select table
    public static DataTable SelectTable(string strSQL)
    {
        DataTable val = new DataTable();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            SqlDataAdapter daSql = new SqlDataAdapter(cmdSql);
            daSql.Fill(val);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            connSql.Close();
        }
        return val;
    }
    public static DataTable SelectTable(string strSQL, SqlParameter param)
    {
        DataTable val = new DataTable();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            SqlDataAdapter daSql = new SqlDataAdapter(cmdSql);
            cmdSql.Parameters.Add(new SqlParameter(param.ParameterName, param.Value));
            daSql.Fill(val);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static DataTable SelectTable(string strSQL, SqlParameter[] param)
    {
        DataTable val = new DataTable();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            for (int i = 0; i < param.Length; i++)
                cmdSql.Parameters.Add(new SqlParameter(param[i].ParameterName, param[i].Value));
            SqlDataAdapter daSql = new SqlDataAdapter(cmdSql);
            daSql.Fill(val);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    #endregion
    #region Load page
    public static DataTable LoadPage(string strSQL, int startRecord, int page_size)
    {
        DataSet ds = new DataSet();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            SqlDataAdapter daSql = new SqlDataAdapter(cmdSql);
            daSql.Fill(ds, startRecord, page_size, "result");
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            connSql.Close();
        }
        return ds.Tables[0];
    }
    public static DataTable LoadPage(string strSQL, int startRecord, int page_size, SqlParameter param)
    {
        DataSet ds = new DataSet();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            SqlDataAdapter daSql = new SqlDataAdapter(cmdSql);
            cmdSql.Parameters.Add(new SqlParameter(param.ParameterName, param.Value));
            daSql.Fill(ds, startRecord, page_size, "result");
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return ds.Tables[0];
    }

    public static DataTable LoadPage(string strSQL, int startRecord, int page_size, SqlParameter[] param)
    {
        DataSet ds = new DataSet();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmdSql = new SqlCommand(strSQL, connSql);
            for (int i = 0; i < param.Length; i++)
                cmdSql.Parameters.Add(new SqlParameter(param[i].ParameterName, param[i].Value));
            SqlDataAdapter daSql = new SqlDataAdapter(cmdSql);
            daSql.Fill(ds, startRecord, page_size, "result");
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return ds.Tables[0];
    }
    #endregion

    #region stored procedure
    public static DataTable SelectTableSP(string storedProcedure)
    {
        DataTable val = new DataTable();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(val);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static DataTable SelectTableSP(string storedProcedure, SqlParameter param)
    {
        DataTable val = new DataTable();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            cmd.Parameters.Add(param);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(val);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static DataTable SelectTableSP(string storedProcedure, SqlParameter[] param)
    {
        DataTable val = new DataTable();
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            for (int i = 0; i < param.Length; i++)
                cmd.Parameters.Add(param[i]);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(val);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }

    public static int ExecuteSP(string storedProcedure)
    {
        int val = 0;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            val = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static int ExecuteSP(string storedProcedure, SqlParameter param)
    {
        int val = 0;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            cmd.Parameters.Add(param);
            val = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    public static int ExecuteSP(string storedProcedure, SqlParameter[] param)
    {
        int val = 0;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        try
        {
            connSql.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connSql;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedure;
            for (int i = 0; i < param.Length; i++)
                cmd.Parameters.Add(param[i]);
            val = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {

            connSql.Close();
        }
        return val;
    }
    #endregion

    public static bool ExecuteTransation(string[] sqlstr)
    {
        bool bRet = true;
        GetConnection();
        SqlConnection connSql = new SqlConnection(strConn);
        SqlTransaction trans;
        connSql.Open();
        trans = connSql.BeginTransaction();
        try
        {
            for (int i = 0; i < sqlstr.Length; i++)
            {
                SqlCommand cmd = new SqlCommand(sqlstr[i], connSql, trans);
                cmd.ExecuteNonQuery();
            }
            trans.Commit();

        }
        catch
        {
            trans.Rollback();
            bRet = false;
        }
        finally
        {

            connSql.Close();
        }
        return bRet;
    }


}
//}